package attend.dao.login;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;

import attend.com.CodeMetrix;

import exception.BaseException;

import util.DBConnection;;

/**
 * @author Yang D
 * @since	2009/02/09
 */
/*
 * 수정이력 : 2011-10-18 (최병옥)
 */
public class LoginDao extends DBConnection {
	
	private Connection 			con			=			null;
	
	private PreparedStatement 	pstmt		=			null;
	
	private ResultSet			rs			=			null;
	
	private StringBuffer		sql			=		new StringBuffer();
	
	private List<HashMap<String, String>>	list;
	
	private HashMap<String , HashMap<String, String>>	menu_auth;
	private HashMap<String , HashMap<String, String>>	main_menu;

	//사원개인정보
	private HashMap<String, String> 		empInfo;
	
	//메뉴접근 권한
	private HashMap<String, String> 		authMenu;
	private HashMap<String, String> 		mainMenu;
	
	
	private String				msg;
	
	public LoginDao() {}

	/**
	 * <pre>
	 * 로그인 정보를 이용하여 사원에 대한 정보가 있는지 조회한다.
	 * </pre>
	 * @param user_id	-	ID
	 * @param pass		-	PWD
	 * @throws BaseException
	 * @since	2009/02/09
	 * @author Yang D
	 */
	public void retrieveEmp(String user_id, String pass) throws BaseException {
		
		try {
			con	=	super.getDataSource().getConnection();
			
			System.out.println("Connection success............................");
			
			sql.setLength(0);
			
			sql.append("			 SELECT	COUNT(*)     CNT        			\r\n");
			sql.append("			 FROM	TB_EMP               				\r\n");
			sql.append("			 WHERE	USER_ID			=	?    			\r\n");
			sql.append("			 AND		USER_PWD	=	?    			\r\n");
			
//			System.out.println("----------------------------------------------------------------------");
//			System.out.println("로그인정보확인\n" + sql.toString());
//			System.out.println("----------------------------------------------------------------------");
			
			pstmt	=	con.prepareStatement(sql.toString());
			
			pstmt.setString(1, user_id);	//ID
			pstmt.setString(2, pass);		//PWD
			
			rs		=	pstmt.executeQuery();
			
			int cnt	=	0;

			while (rs.next()) {
				cnt = rs.getInt("CNT");	//
				
				if (cnt == 0) {
					setMsg("ID와 PASSWORD를 올바르게 입력하시기 바랍니다.");
				} else if (cnt > 1) {
					setMsg("사용자가 많습니다. \\r\\n관리자에게 문의하시기 바랍니다.");
				}
			}
			
			//사원정보가 1인 경우 사원정보를 가져온다. 
			if (cnt == 1) {
				
				sql.setLength(0);
				
				sql.append("			SELECT                                                       			\r\n");
				sql.append("				G.DEPT_NO					GROUP_NO		,            			\r\n");
				sql.append("				G.DEPT_NM					GROUP_NM		,            			\r\n");
				sql.append("				D.DEPT_NO					TEAM_NO			,            			\r\n");
				sql.append("				D.DEPT_NM					TEAM_NM			,            			\r\n");
				sql.append("				E.EMP_NO									,            			\r\n");
				sql.append("				E.EMP_NM									,            			\r\n");
				sql.append("				E.USER_ID									,            			\r\n");
				sql.append("				E.RANK_NM					RANK_NO			,            			\r\n");
				sql.append("				C.CD_NM						RANK_NM			,            			\r\n");
				sql.append("				E.POSITION					POSITION_NO		,            			\r\n");
				sql.append("				C1.CD_NM					POSITION_NM		,            			\r\n");
				sql.append("				ISNULL(A.APPROV_YN, 'N')	APPROV_YN       ,             			\r\n");
				sql.append("				(SELECT	CONVERT(VARCHAR(4) ,YEAR(GETDATE())) + '년 '+ CONVERT(VARCHAR(2) ,MONTH(GETDATE()))+'월 ' +CONVERT(VARCHAR(2) , DAY(GETDATE()))+'일 <b>'+ DATENAME(DW , GETDATE())+'</b>')        TODAY             			\r\n");
				sql.append("			FROM	                                                     			\r\n");
				sql.append("					TB_EMP E JOIN TB_DEPT D                              			\r\n");
				sql.append("								ON	E.FK_DEPT_NO			=	D.DEPT_NO			\r\n");
				sql.append("								AND	E.USER_ID				=	?        			\r\n");
				sql.append("								AND E.USER_PWD				=	?        			\r\n");
				sql.append("								AND E.WORK_YN				=	'Y'      			\r\n");
				sql.append("								AND D.USE_YN				=	'Y'      			\r\n");
				sql.append("							JOIN	TB_DEPT G                            			\r\n");
				sql.append("								ON	D.UPPER_DEPT_NO			=	G.DEPT_NO			\r\n");
				sql.append("								AND	G.USE_YN				=	D.USE_YN 			\r\n");
				//조건 추가함 
				sql.append("								AND (GETDATE() BETWEEN E.APPLY_DT AND E.RETIRE_DT OR ISNULL(RETIRE_DT,'') = '')			\r\n");			
				sql.append("							JOIN	TB_COMM_CD C                         			\r\n");
				sql.append("								ON	E.RANK_NM				=	C.CD     			\r\n");
				sql.append("							JOIN	TB_COMM_CD C1                        			\r\n");
				sql.append("								ON	E.POSITION				=	C1.CD    			\r\n");
				sql.append("							LEFT JOIN	TB_AUTH_DEPT A                   			\r\n");
				sql.append("								ON	E.EMP_NO				=	A.EMP_NO 			\r\n");
				sql.append("								AND	A.USE_YN				=	'Y'      			\r\n");
				
				System.out.println("--------------------------------------------------------------------------------");
				System.out.println(sql.toString());
				System.out.println("--------------------------------------------------------------------------------");
				
				pstmt	=	con.prepareStatement(sql.toString());
				
				pstmt.setString(1, user_id);
				pstmt.setString(2, pass);
				
				rs		=	pstmt.executeQuery();
				
				if (rs.next()) {
					
					empInfo	=	new HashMap<String, String>();
					
					empInfo.put("group_no"		, rs.getString("GROUP_NO"));
					empInfo.put("group_nm"		, rs.getString("GROUP_NM"));
					empInfo.put("team_no"		, rs.getString("TEAM_NO"));
					empInfo.put("team_nm"		, rs.getString("TEAM_NM"));
					empInfo.put("emp_no"		, rs.getString("EMP_NO"));
					empInfo.put("emp_nm"		, rs.getString("EMP_NM"));
					empInfo.put("user_id"		, rs.getString("USER_ID"));
					empInfo.put("rank_no"		, rs.getString("RANK_NO"));
					empInfo.put("rank_nm"		, rs.getString("RANK_NM"));
					empInfo.put("position_no"	, rs.getString("POSITION_NO"));
					empInfo.put("position_nm"	, rs.getString("POSITION_NM"));
					empInfo.put("approv_yn"		, rs.getString("APPROV_YN"));			//권한
					empInfo.put("today"			, rs.getString("TODAY"));			//권한
				}
//				System.out.println("----------------------------------------------");
//				System.out.println(empInfo);
//				System.out.println("----------------------------------------------");
			}	//사원정보가 1인 경우 사원정보를 가져온다.
			
		} catch (SQLException se) {
			se.printStackTrace();
			BaseException dex = new BaseException();                
			dex.setException(se);
			dex.setAppReturnCode("10");
			throw dex;
		} catch(Exception e) {
			BaseException dex = new BaseException();                
			dex.setException(e);
			dex.setAppReturnCode("20");
			throw dex;
		} finally {
			try {
				if (pstmt != null) pstmt.close();
				if (rs != null) rs.close();
				if(con != null) con.close();

			} catch(SQLException fse) {
				BaseException dex = new BaseException();                
				dex.setException(fse);
				dex.setAppReturnCode("10");
				throw dex;
			}          
		}
	}	//retrieveEmp
	
	/**
	 * <pre>사원의 메뉴권한여부를  모두 가져온다.</pre>
	 * @param emp_no
	 * @return
	 * @throws BaseException
	 * @author Yang D
	 * @since	2009-03-25
	 */
	public HashMap<String , HashMap<String, String>> retrieveAuthMenuList(int emp_no) throws BaseException {
		
		try {
			
			con	=	getDataSource().getConnection();
			
			sql.setLength(0);
/*			
			sql.append("			SELECT	M.MENU_CODE , A.AUTH , A.ACCESS_YN , A.USE_YN , M.MENU_NM , M.MENU_URL			\n");
			sql.append("			FROM	ADMIN_MENU_AUTH_TB	A                                             				\n");
			sql.append("			JOIN	TB_EMP			E                                             					\n");
			sql.append("			ON		A.AUTH = E.POSITION                                                   			\n");
			sql.append("			AND		E.EMP_NO	=	" + emp_no + "                                           		\n");
			sql.append("			JOIN	ADMIN_MENU_TB		M                                             				\n");
			sql.append("			ON		A.MENU_URL = M.MENU_CODE                                              			\n");
			sql.append("			AND		A.MENU_URL != '1100'                                                  			\n");
			*/

			sql.append("Select  a.Menu_Group , a.Menu_Group_Sort, lower(a.Menu_URL) Menu_URL, a.Menu_NM	,b.auth				\n");
			sql.append("From Admin_Menu_TB a, admin_menu_auth_tb b , TB_EMP c												\n");
			sql.append("Where a.menu_code = b.menu_url 																		\n");
			sql.append("And a.use_yn = 'Y'																					\n");
			sql.append("And b.access_yn = 'Y'																				\n");
			sql.append("And a.MENU_SORT = '0'																				\n");
			sql.append("And c.EMP_NO = " + emp_no + " 																		\n");
			sql.append("And b.AUTH = c.POSITION              																\n");
			sql.append("Order By Menu_Group_Sort ASC, Menu_Sort ASC 														\n");

			System.out.println("--------------------------------------------------------------------------------");
			System.out.println(sql.toString());
			System.out.println("--------------------------------------------------------------------------------");
			
			pstmt	=	con.prepareStatement(sql.toString());
			
			rs		=	pstmt.executeQuery();
			
			//menu_auth	=	new HashMap<String , HashMap<String, String>>();
			main_menu	=	new HashMap<String , HashMap<String, String>>();
			
			list = new ArrayList<HashMap<String, String>>();
			
			while (rs.next()) {
				
				mainMenu	=	new HashMap<String, String>();
				/*
				authMenu.put("menu_code"	, rs.getString("MENU_CODE"));
				authMenu.put("auth"			, rs.getString("AUTH"));
				authMenu.put("access_yn"	, rs.getString("ACCESS_YN"));
				authMenu.put("menu_url"		, rs.getString("MENU_URL"));				
				*/
				
				mainMenu.put("menu_group"				, rs.getString("Menu_Group"));
				mainMenu.put("menu_group_sort"			, rs.getString("Menu_Group_Sort"));
				mainMenu.put("menu_url"					, rs.getString("Menu_URL"));
				mainMenu.put("menu_nm"					, rs.getString("Menu_NM"));
				mainMenu.put("auth"						, rs.getString("auth"));
	
				//menu_auth.put(rs.getString("MENU_CODE") , authMenu);	//메뉴별코드가 키값이다.
				//main_menu.put(rs.getString("Menu_Group") , mainMenu);
				list.add(mainMenu);
				
			}
			
//			System.out.println("~~~~~~~DAODAODAODAODAODAODAODAODAO~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~");
//			System.out.println(menu_auth);
//			System.out.println("~~~~~~~DAODAODAODAODAODAODAODAODAO~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~");
			
		} catch (SQLException se) {
			se.printStackTrace();
			BaseException dex = new BaseException();                
			dex.setException(se);
			dex.setAppReturnCode("10");
			throw dex;
		} catch(Exception e) {
			BaseException dex = new BaseException();                
			dex.setException(e);
			dex.setAppReturnCode("20");
			throw dex;
		} finally {
			try {
				if (pstmt != null) pstmt.close();
				if (rs != null) rs.close();
				if (con != null) con.close();

			} catch(SQLException fse) {
				BaseException dex = new BaseException();                
				dex.setException(fse);
				dex.setAppReturnCode("10");
				throw dex;
			}          
		}
		
		return main_menu;
		
	}

	/************************************************************************************************************/
	public HashMap<String, String> getEmpInfo() {
		return empInfo;
	}

	public void setEmpInfo(HashMap<String, String> empInfo) {
		this.empInfo = empInfo;
	}

	public List<HashMap<String, String>> getList() {
		return list;
	}

	public void setList(List<HashMap<String, String>> list) {
		this.list = list;
	}

	public String getMsg() {
		return msg;
	}

	public void setMsg(String msg) {
		this.msg = msg;
	}
	
	public HashMap<String, String> getMainMenu() {
		return mainMenu;
	}

	public void setMainMenu(HashMap<String, String> mainMenu) {
		this.mainMenu = mainMenu;
	}




	/************************************************************************************************************/
	
	private StringBuffer sb = new StringBuffer();
	
	/**
	 * 부서  권한 체크
	 * @param user_id	-아이디
	 * @param password	-암호
	 * @return
	 * @throws BaseException
	 */
	public boolean check_Admin(String user_id, String pass) throws BaseException{

		try{
			
			con = DBConnection.getDataSource().getConnection();
			
			sb.setLength(0);
			
			sb.append("  			SELECT 	COUNT(*)                		  		\n");
			sb.append("  			FROM 	TB_EMP E                		  		\n");
			sb.append("  			JOIN 	TB_AUTH_DEPT A          		  		\n");
			sb.append("  				ON 	E.EMP_NO 	= 	A.EMP_NO		  		\n");
			sb.append("  				AND A.APPROV_YN	=	'Y'       		  		\n");
			sb.append("  				AND A.USE_YN	=	'Y'       		  		\n");
			sb.append("  				AND E.USER_PWD	=	?       		  		\n");
			sb.append("  				AND E.USER_PWD	=	?       		  		\n");
			
//			System.out.println("------------------------------------------------------------------");
//			System.out.println(sb.toString());
//			System.out.println("------------------------------------------------------------------");

			pstmt = con.prepareStatement(sb.toString());
			pstmt.setString(1, user_id);
			pstmt.setString(2, pass);
			
			rs = pstmt.executeQuery();

			if(rs.next()){
				System.out.println("관리자 맞음.");
				return true;
			}else{
				System.out.println("관리자 아님.");
				return false;
			}

		}catch (SQLException se){
			se.printStackTrace();
			BaseException dex = new BaseException();                
			dex.setException(se);
			dex.setAppReturnCode("10");
			throw dex;
		} catch(Exception e){
			e.printStackTrace();
			BaseException dex = new BaseException();                
			dex.setException(e);
			dex.setAppReturnCode("20");
			throw dex;
		} finally{
			try{
				if (pstmt != null) pstmt.close();
				if (rs != null) rs.close();
				if(con != null) con.close();
			} catch(SQLException fse){
				BaseException dex = new BaseException();                
				dex.setException(fse);
				dex.setAppReturnCode("10");
				throw dex;
			} 
		}
	}

	/**
	 * 아이디 체크
	 * @param user_id
	 * @return
	 * @throws BaseException
	 */
	public boolean check_ID(String user_id) throws BaseException{

		try {
			
			con = DBConnection.getDataSource().getConnection();
			
			sb.setLength(0);

			sb.append("  			SELECT 	COUNT(*)                		  		\n");
			sb.append("  			FROM 	TB_EMP E                		  		\n");
			sb.append("  			JOIN 	TB_AUTH_DEPT A          		  		\n");
			sb.append("  				ON 	E.EMP_NO 	= 	A.EMP_NO		  		\n");
			sb.append("  				AND E.USER_ID 	= 	?       		  		\n");

//			System.out.println("------------------------------------------------------------------");
//			System.out.println(sb.toString());
//			System.out.println("------------------------------------------------------------------");

			pstmt = con.prepareStatement(sb.toString());
			
			pstmt.setString(1, user_id);
			
			rs = pstmt.executeQuery();
			System.out.println("id ====> "+user_id);
			
			if(rs.next()){
				System.out.println("해당아이디 있음.");
				return true;
			}else{
				System.out.println("해당아이디 없음.");
				return false;
			}

		}catch (SQLException se){
			se.printStackTrace();
			BaseException dex = new BaseException();                
			dex.setException(se);
			dex.setAppReturnCode("10");
			throw dex;
		} catch(Exception e){
			e.printStackTrace();
			BaseException dex = new BaseException();                
			dex.setException(e);
			dex.setAppReturnCode("20");
			throw dex;
		} finally{
			try{
				if (pstmt != null) pstmt.close();
				if (rs != null) rs.close();
				if(con != null) con.close();
			} catch(SQLException fse){
				BaseException dex = new BaseException();                
				dex.setException(fse);
				dex.setAppReturnCode("10");
				throw dex;
			} 
		}
	}
	
	/**
	 * 암호체크
	 * @param password
	 * @return
	 * @throws BaseException
	 */
	public boolean check_PW(String password) throws BaseException{


		try{
			
			con = DBConnection.getDataSource().getConnection();
			
			sb.setLength(0);
			
			sb.append("  SELECT * FROM ADMIN_TB WHERE PSWD = ?  \n");

			pstmt = con.prepareStatement(sb.toString());
			pstmt.setString(1, password);
			
			rs = pstmt.executeQuery();
			
			if(rs.next()){
				System.out.println("비밀번호 맞음.");
				return true;
			}else{
				System.out.println("비밀번호 틀림.");
				return false;
			}

		}catch (SQLException se){
			se.printStackTrace();
			BaseException dex = new BaseException();                
			dex.setException(se);
			dex.setAppReturnCode("10");
			throw dex;
		} catch(Exception e){
			e.printStackTrace();
			BaseException dex = new BaseException();                
			dex.setException(e);
			dex.setAppReturnCode("20");
			throw dex;
		} finally{
			try{
				if (pstmt != null) pstmt.close();
				if (rs != null) rs.close();
				if(con != null) con.close();
			} catch(SQLException fse){
				BaseException dex = new BaseException();                
				dex.setException(fse);
				dex.setAppReturnCode("10");
				throw dex;
			} 
		}
	}
	
	/**
	 * 세션 생성
	 * @return String
	 * @throws BaseException
	 */
	public String createSession() throws BaseException{

		try{	
			con = DBConnection.getDataSource().getConnection();
			
			String sessionTime = "";
			
			sb.setLength(0);
			sb.append("  SELECT getDate() AS CREATESESSION    \n");

			pstmt = con.prepareStatement(sb.toString());
			rs = pstmt.executeQuery();
			
			if(rs.next()){
				System.out.println("세션생성");
				sessionTime = rs.getString("CREATESESSION");
			}
			
			return sessionTime;

		}catch (SQLException se){
			se.printStackTrace();
			BaseException dex = new BaseException();                
			dex.setException(se);
			dex.setAppReturnCode("10");
			throw dex;
		} catch(Exception e){
			e.printStackTrace();
			BaseException dex = new BaseException();                
			dex.setException(e);
			dex.setAppReturnCode("20");
			throw dex;
		} finally{
			try{
				if (pstmt != null) pstmt.close();
				if (rs != null) rs.close();
				if(con != null) con.close();
			} catch(SQLException fse){
				BaseException dex = new BaseException();                
				dex.setException(fse);
				dex.setAppReturnCode("10");
				throw dex;
			} 
		}
	}

	/**
	 * <pre>비밀번호 변경</pre>
	 * @param emp_no		- 	사번
	 * @param user_id		-	아이디
	 * @param current_pwd	-	현재 비번
	 * @param new_pwd_1		-	변경할 비번
	 * 
	 * @return	String		-	{@link CodeMetrix} OK , FAILE
	 * @throws BaseException
	 * @since 2009/03/05
	 */
	public String updatePassword(int emp_no , String user_id , String current_pwd , String new_pwd_1) throws BaseException {
		
		int cnt = 	0;
		int rtn	=	0;
		
		try {
			con	=	getDataSource().getConnection();
			
			sb.setLength(0);
			
			sb.append("					SELECT 		COUNT(*)        CNT       					\n");
			sb.append("					FROM 		TB_EMP                 						\n");
			sb.append("					WHERE 		EMP_NO 		= 	"+emp_no+"    				\n");
			sb.append("						AND 	USER_ID 	=	'"+user_id+"'				\n");
			sb.append("						AND 	USER_PWD 	= 	'"+current_pwd+"'			\n");
			
//			System.out.println("-------------------------------------------------------------------------------------");
//			System.out.println("현재 비밀번호가 맞는지 확인\n" + sb.toString());
//			System.out.println("-------------------------------------------------------------------------------------");
			
			pstmt	=	con.prepareStatement(sb.toString());
			rs		=	pstmt.executeQuery();
			
			
			if (rs.next()) {
				cnt	=	rs.getInt("CNT");
			}
			
			if (cnt < 1) {
				setMsg(CodeMetrix.NOTEXIST);
				return msg;
			}
			
			con.setAutoCommit(false);
			
			sb.setLength(0);
			
			sb.append("					UPDATE  TB_EMP            						\n");
			sb.append("					SET     USER_PWD    =   '"+new_pwd_1+"' 		\n");
			sb.append("					WHERE   EMP_NO      =   "+emp_no+" 				\n");
			sb.append("					    AND USER_ID     =   '"+user_id+"' 			\n");
			sb.append("					    AND USER_PWD	=	'"+current_pwd+"' 		\n");
			
//			System.out.println("-----------------------------------------------------------------------");
//			System.out.println("비빌번호 변경 쿼리 \n" + sql.toString());
//			System.out.println("-----------------------------------------------------------------------");

			pstmt = con.prepareStatement(sb.toString());
			
			rtn = pstmt.executeUpdate();
			
			con.setAutoCommit(true);
			
			if (rtn < 1) {
				setMsg(CodeMetrix.FAILE);
				return msg;
			}
			
		}catch (SQLException se){
			try {
				con.rollback();	//rollback
			} catch (SQLException e) {
				e.printStackTrace();
			}
			se.printStackTrace();
			BaseException dex = new BaseException();                
			dex.setException(se);
			dex.setAppReturnCode("10");
			throw dex;
		} catch(Exception e){
			e.printStackTrace();
			BaseException dex = new BaseException();                
			dex.setException(e);
			dex.setAppReturnCode("20");
			throw dex;
		} finally{
			try{
				if (pstmt != null) pstmt.close();
				if (rs != null) rs.close();
				if (con != null) con.close();
			} catch(SQLException fse){
				BaseException dex = new BaseException();                
				dex.setException(fse);
				dex.setAppReturnCode("10");
				throw dex;
			} 
		}
		
		setMsg(CodeMetrix.OK);
		return msg;
		
	}
	
	
	/**
	 * @return the menu_auth
	 */
	public HashMap<String, HashMap<String, String>> getMenu_auth() {
		return menu_auth;
	}

	/**
	 * @param menu_auth the menu_auth to set
	 */
	public void setMenu_auth(HashMap<String, HashMap<String, String>> menu_auth) {
		this.menu_auth = menu_auth;
	}

}
